package day20180408;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class oneKeyTest1 {

    public static void main(String[] args) {

        //定义一个学生类装学生信息
        class Student {

            int id;
            String name;
            String sex;
            String place;
            float grade;

            public Student(int id, String name, String sex, String place, float grade) {
                this.id = id;
                this.name = name;
                this.sex = sex;
                this.place = place;
                this.grade = grade;
            }

            public Student(int id) {
                this.id = id;
            }
        }

        //按照男女比例成绩排序分班，list1为男生组，list2为女生组
        List<Student> list1 = new ArrayList<>();
        List<Student> list2 = new ArrayList<>();


        //3+1从数据库获取数据
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;

        //查询数据库，照成绩排序
        String sql = "select * from test1 order by grade DESC";

        try {
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/sctu_system_test?serverTimezone = UTC",
                    "root",
                    "123456"
            );


            statement = connection.createStatement();
            resultset = statement.executeQuery(sql);


            //用循环将学生放入list中
            while (resultset.next()) {

                int id = resultset.getInt(1);
                String name = resultset.getString(3);
                String sex = resultset.getString(7);
                String place = resultset.getString(6);
                float grade = resultset.getFloat(5);


                //判定性别，分别放入对用的实体类
                if (sex.equals("男")) {
                    Student student1 = new Student(id, name, sex, place, grade);
                    list1.add(student1);
                } else {
                    Student student2 = new Student(id, name, sex, place, grade);
                    list2.add(student2);
                }

            }

            //输出测试，校验结果正确
            for (Student student1 : list1) {
                System.out.println(student1.id + "\t" + student1.name + "\t" + student1.sex + "\t"
                        + student1.grade + "\t" + student1.place);
            }
            for (Student student2 : list2) {
                System.out.println(student2.id + "\t" + student2.name + "\t" + student2.sex + "\t"
                        + student2.grade + "\t" + student2.place);
            }

            //按照成绩排序取余，照余数进行分班
            for (int i = 0; i < list1.size(); i++) {
                int j = i % 3 + 1;
                String sql1 = "UPDATE test1 SET class = '" + j + "' WHERE id = '" + list1.get(i).id + "'";
                statement = connection.createStatement();
                statement.execute(sql1);
            }

            for (int i = 0; i < list2.size(); i++) {
                int j = i % 3 + 1;
                String sql2 = "update test1 set class = '" + j + "' where id = '" + list2.get(i).id + "'";
                statement = connection.createStatement();
                statement.execute(sql2);
            }


            //按照班级分学号
            String sql3 = "select * from test1 order by class";
            resultset = statement.executeQuery(sql3);


            List<Student> list = new ArrayList<>();
            while (resultset.next()) {

                int id = resultset.getInt(1);
                Student student = new Student(id);
                list.add(student);
            }

            for (int i = 0; i < list.size(); i++) {

                int j = i + 1;
                String sql4 = "update test1 set sno = '" + j + "' where id = '" + list.get(i).id + "'";
                statement.execute(sql4);

            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        /*
        * 该程序代码还不完善，目前具体问题如下：
        * 1.男女比例同年级同专业是否各个班比例一致，如不一致，则需要调整分班部分代码
        * 2.插入学号部分，需要提取年级专业代码部分，没有进行设置，需要补充，同专业各班直接进行循环增加
        * */
    }

}
